<?php 
include_once 'excel_head.php'; 

if (isset($_POST['fd'] , $_POST['fh'] , $_POST['el'])) {
    $fechaDesde = $_POST['fd']; 
    $fechaHasta = $_POST['fh']; 
    $estadoLiquidacion = $_POST['el']; 
    $operarios = $_POST['es']; 
} else {
    // The correct POST variables were not sent to this page. 
    header('Location: main.php?pag=rp');
}

function empresaSesion() {
	return $_SESSION['empresa'];
}

// New Connection
$db = new mysqli(PHPGRID_DB_HOSTNAME, PHPGRID_DB_USERNAME, PHPGRID_DB_PASSWORD, PHPGRID_DB_NAME);

// Check for errors
if(mysqli_connect_errno()){
 echo mysqli_connect_error();
}

$estado = "Ambos";
if ($estadoLiquidacion) {
	if ($estadoLiquidacion == 'L') { 
		$estado = "Liquidado";
		}
	if ($estadoLiquidacion == 'P') { 
		$estado = "Pendiente";
		}
} 

$reporte->setActiveSheetIndex(0)->setCellValue("F2","PRODUCTIVIDAD CONSOLIDADA POR OPERARIOS"); 
$reporte->setActiveSheetIndex(0)->setCellValue("F3","Fecha Desde: ".$fechaDesde." - Fecha Hasta: ".$fechaHasta." - Estado Liquidacion: ".$estado); 

//echo $operarios;

$result = $db->query("SELECT ID_OPERARIO, NOMBRE FROM serverinstall.operarios where ID_OPERARIO in (".$operarios.")");

if($result){
     // Cycle through results
    while ($row = $result->fetch_array()){
			$ops[] = $row;
    }
    // Free result set
    $result->close();
    //$db->next_result();
}

$primeraVez = 0;
$fila = 9;
$sheetId = 0;

foreach ($ops as $op) {


//			if ($primeraVez == 0) {
//				$sheetId = $sheetId + 1;
//				$reporte->createSheet();
//				$reporte->setActiveSheetIndex($sheetId);
//				$fila = 1;
//				$primeraVez = 1;
//			}
			$inval = array("/");
			$val = array("-");

			$nombresheet = str_replace($inval, $val, $op['NOMBRE']);
			$nombresheet = substr($nombresheet, 0, 30);
			$reporte->getActiveSheet()->setTitle($nombresheet);

			$reporte->setActiveSheetIndex($sheetId)->setCellValue("B$fila","Operario"); 
			$reporte->setActiveSheetIndex($sheetId)->setCellValue("C$fila",$op['NOMBRE']);  

			$fila = $fila + 2;

			$reporte->setActiveSheetIndex($sheetId)->setCellValue("A$fila"," "); 
			$reporte->setActiveSheetIndex($sheetId)->setCellValue("B$fila","Fecha"); 
			$reporte->setActiveSheetIndex($sheetId)->setCellValue("C$fila","Nro. OT"); 
			$reporte->setActiveSheetIndex($sheetId)->setCellValue("D$fila","Direccion"); 
			$reporte->setActiveSheetIndex($sheetId)->setCellValue("E$fila","Nro. Reingreso"); 			
			//$reporte->setActiveSheetIndex($sheetId)->setCellValue("E$fila","Cod Items"); 
			$reporte->setActiveSheetIndex($sheetId)->setCellValue("F$fila","Item"); 
			$reporte->setActiveSheetIndex($sheetId)->setCellValue("G$fila","Cantidad"); 
			$reporte->setActiveSheetIndex($sheetId)->setCellValue("H$fila","Importe"); 
			$reporte->setActiveSheetIndex($sheetId)->setCellValue("I$fila","Estado Liq"); 
			$reporte->getActiveSheet()->getColumnDimension('A')->setWidth(1);
			$reporte->getActiveSheet()->getColumnDimension('B')->setWidth(13.2);
			$reporte->getActiveSheet()->getColumnDimension('C')->setWidth(11.5);
			$reporte->getActiveSheet()->getColumnDimension('D')->setWidth(50);
			$reporte->getActiveSheet()->getColumnDimension('E')->setWidth(20);
			$reporte->getActiveSheet()->getColumnDimension('F')->setWidth(85);
			$reporte->getActiveSheet()->getColumnDimension('G')->setWidth(10);
			$reporte->getActiveSheet()->getColumnDimension('H')->setWidth(10);
			$reporte->getActiveSheet()->getColumnDimension('I')->setWidth(50);
			$reporte->getActiveSheet()->getColumnDimension('J')->setWidth(15);

			for ( $i = 1 ; $i < 10 ; $i ++) {
			
				$reporte->getActiveSheet()
				    ->getStyleByColumnAndRow($i, $fila)
				    ->getFont()->applyFromArray($style['label']);
				
				$reporte->getActiveSheet()
				    ->getStyleByColumnAndRow($i, $fila)
						->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
				
				$reporte->getActiveSheet()
				    ->getStyleByColumnAndRow($i, $fila)
						->getFill()->getStartColor()->setRGB('E4EAF4');
			
			}

			$total = 0;
			$fila = $fila + 1;
			
			$result = $db->query("CALL `serverinstall`.`productividadOperarios`('".$fechaDesde."', '".$fechaHasta."', ".$op['ID_OPERARIO'].", '".$estadoLiquidacion."',".empresaSesion().")");
			if($result){
			     // Cycle through results
			    while ($row = $result->fetch_object()){
			    	if ($row->LIQUIDADO == 1) {
			    		$estadoLiq = "Liquidado";
			    	} 
			    	else {
			    		$estadoLiq = "Pendiente";
			    		}
			    	$reporte->setActiveSheetIndex($sheetId)->setCellValue("B$fila",$row->FECHA_ESTADO);  
						$reporte->setActiveSheetIndex($sheetId)->setCellValue("C$fila",$row->NRO_ORDEN_TRABAJO);  
						$reporte->setActiveSheetIndex($sheetId)->setCellValue("D$fila",utf8_encode($row->DIRECCION));  
						$reporte->setActiveSheetIndex($sheetId)->setCellValue("E$fila",$row->NRO_REINGRESO);  
						//$reporte->setActiveSheetIndex($sheetId)->setCellValue("E$fila",$row->CODIGO_ITEM);  
						$reporte->setActiveSheetIndex($sheetId)->setCellValue("F$fila",utf8_encode($row->DESC_ITEM));  
						$reporte->setActiveSheetIndex($sheetId)->setCellValue("G$fila",$row->CANTIDAD);  
						$reporte->setActiveSheetIndex($sheetId)->setCellValue("H$fila",$row->FACTURADO);  
						$reporte->setActiveSheetIndex($sheetId)->setCellValue("I$fila",$estadoLiq);  
						$total = $total + $row->FACTURADO;
						$fila = $fila + 1;
			    }
			    
			    $fila = $fila + 1;
			    $reporte->getActiveSheet()->getStyle("G$fila:H$fila")->getFont()->setBold(true);
					$reporte->setActiveSheetIndex($sheetId)->setCellValue("G$fila","TOTAL"); 
					$reporte->setActiveSheetIndex($sheetId)->setCellValue("H$fila",'$ '.$total); 
			    
			    // Free result set
			    $result->close();
			    $db->next_result();
			}
			else echo($db->error);

			$sheetId = $sheetId + 1;
			$reporte->createSheet();
			$reporte->setActiveSheetIndex($sheetId);
			$fila = 1;


//			$fila = $fila + 1;

}

$reporte->setActiveSheetIndex(0);

// Close connection
$db->close();

$objWriter = PHPExcel_IOFactory::createWriter($reporte, 'Excel2007'); 

//ob_end_clean();

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="productividad_operarios.xlsx"');
header('Cache-Control: max-age=0'); 	

$objWriter->save('php://output');
?>